Индексы в PostgreSQL

CREATE INDEX

В PostgreSQL, чтобы оптимизировать запрос и создать индекс, нужно выполнить команду

CREATE INDEX book__author_year__index ON book (author, year,....)

При создании индексов можно применять функции(Если мы ищем по результату функции, то необходим индекс именно по функции)
Пример, приведение к нижнему регистру

CREATE INDEX book__lower_author__index ON book(lower(author))

PostgreSQL предоставляет выбор типов индексов, через конструкцию USING

CREATE INDEX book__author__index ON book USING HASH (author)

Виды индексов

B-tree Полезен когда сравнение больше меньше или сравнение по какой то части от строки
Hash Полезен когда сравниваем именно равенством
GiST Используется когда сравнения больше и меньше не целесооразны, например геоданные, строит R-дерево
И другие...

Составной индекс

PostgreSQL сортирует лекссикографически, то есть сначала по первому столбцу, а затем — по второму (в пределах одинаковых значений первого столбца) затем по третьему итд. Это приводит к тому, что поиск сразу по всем полям будет быстрый, поиск только по первому будет также быстр, но только по второму/третьему итд уже почти не эффективен.

 CREATE INDEX idx_name ON table_name (column1, column2);

Последовательное сканирование

В некоторых случаях оптимизатор будет избегать использование индексов. Это может происходить например в случае если у запроса большая селективность, то есть условие Where удавлетворяет много записей. В таком случае оптимизатор решит просто подряд читать всю таблицу, тк это быстрее чем прыгать по указателям.

Частичные индексы

Иногда, в одной и той же таблице могут происходить как запросы с высокой селективностью так и с низкой. В таких случаях могут пригодиться частичные индексы. Например, в таблице 90% записей имеют в поле is_translated значение true. Тогда в случае если мы ищем значения с полем is_translated = true выгоднее использовать полное сканирование, а если false то выгоднее искать по индексу. В таком случае можно создать частичный индекс.

CREATE INDEX ON book(is_translated) where is_translated;

Параллельное построение

Когда происходит построение индекса, таблица блокируется, однако существуют команды которые позволят не блокировать таблицу, но тогда процесс построения замедлится, тк читать придется дважды, перед началом создания индекса и после.

CREATE INDEX CONCURRENTLY ON book(author);